﻿--use PolePosition
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Map_SearchCriteriaIdLists')
	DROP PROCEDURE up_Map_SearchCriteriaIdLists
GO

CREATE PROCEDURE [dbo].[up_Map_SearchCriteriaIdLists]

	 @XmissionGpsProjectList varchar(MAX) = null
	,@XmissionInspectionProjectList varchar(MAX) = null
	,@DistributionInspectionProjectList varchar(MAX) = null
	,@DistributionGpsProjectList varchar(MAX) = null

	,@XmissionAssets bit
	,@DistributionAssets bit
	,@MeterAssets bit
	,@TransformerAssets bit
	,@UtilityFk int

	,@MapList varchar(MAX) = null
	,@SectionList varchar(MAX) = null

	,@XmissionKvList varchar(MAX) = null
	,@XmissionTopsList varchar(MAX) = null
	,@XmissionSpeciesList varchar(MAX) = null
	,@XmissionTreatmentList varchar(MAX) = null
	,@XmissionClassList varchar(MAX) = null
	,@XmissionAgeList varchar(MAX) = null

	,@DistributionPhaseList varchar(MAX) = null
	,@DistributionTopsList varchar(MAX) = null
	,@DistributionSpeciesList varchar(MAX) = null
	,@DistributionTreatmentList varchar(MAX) = null
	,@DistributionClassList varchar(MAX) = null
	,@DistributionAgeList varchar(MAX) = null

	,@MeterNotesList varchar(MAX) = null

	,@ElecticalProblem bit = null
	,@InspectionResultList varchar(MAX) = null
	,@InspectionTypeList varchar(MAX) = null
	,@InspectionVendorList varchar(MAX) = null
	,@InspectionCompanyList varchar(MAX) = null
	,@InspectionNoteList varchar(MAX) = null
AS

SET NOCOUNT ON


DECLARE @Maps table
(
	Map int PRIMARY KEY
)
INSERT @Maps
SELECT DISTINCT value FROM dbo.fn_Split(@MapList, ',')

DECLARE @Sections table
(
	Section int PRIMARY KEY
)
INSERT @Sections
SELECT DISTINCT value FROM dbo.fn_Split(@SectionList, ',')


DECLARE @XmissionKv table
(
	Kv int PRIMARY KEY
)
INSERT @XmissionKv
SELECT DISTINCT value FROM dbo.fn_Split(@XmissionKvList, ',')

DECLARE @XmissionTops table
(
	TopType int PRIMARY KEY
)
INSERT @XmissionTops
SELECT DISTINCT value FROM dbo.fn_Split(@XmissionTopsList, ',')

DECLARE @XmissionSpecies table
(
	Species int PRIMARY KEY
)
INSERT @XmissionSpecies
SELECT DISTINCT value FROM dbo.fn_Split(@XmissionSpeciesList, ',')

DECLARE @XmissionTreatment table
(
	Treatment int PRIMARY KEY
)
INSERT @XmissionTreatment
SELECT DISTINCT value FROM dbo.fn_Split(@XmissionTreatmentList, ',')

DECLARE @XmissionClass table
(
	Class int PRIMARY KEY
)
INSERT @XmissionClass
SELECT DISTINCT value FROM dbo.fn_Split(@XmissionClassList, ',')


DECLARE @XmissionAge table
(
	Age int PRIMARY KEY
)
INSERT @XmissionAge
SELECT DISTINCT value FROM dbo.fn_Split(@XmissionAgeList, ',')


DECLARE @DistributionPhase table
(
	Phase int PRIMARY KEY
)
INSERT @DistributionPhase
SELECT DISTINCT value FROM dbo.fn_Split(@DistributionPhaseList, ',')

DECLARE @DistributionTops table
(
	TopType int PRIMARY KEY
)
INSERT @DistributionTops
SELECT DISTINCT value FROM dbo.fn_Split(@DistributionTopsList, ',')

DECLARE @DistributionSpecies table
(
	Species int PRIMARY KEY
)
INSERT @DistributionSpecies
SELECT DISTINCT value FROM dbo.fn_Split(@DistributionSpeciesList, ',')

DECLARE @DistributionTreatment table
(
	Treatment int PRIMARY KEY
)
INSERT @DistributionTreatment
SELECT DISTINCT value FROM dbo.fn_Split(@DistributionTreatmentList, ',')

DECLARE @DistributionClass table
(
	Class int PRIMARY KEY
)
INSERT @DistributionClass
SELECT DISTINCT value FROM dbo.fn_Split(@DistributionClassList, ',')


DECLARE @DistributionAge table
(
	Age int PRIMARY KEY
)
INSERT @DistributionAge
SELECT DISTINCT value FROM dbo.fn_Split(@DistributionAgeList, ',')




DECLARE @InspectionResult table
(
	Result int PRIMARY KEY
)
INSERT @InspectionResult
SELECT DISTINCT value FROM dbo.fn_Split(@InspectionResultList, ',')


DECLARE @InspectionType table
(
	Type int PRIMARY KEY
)
INSERT @InspectionType
SELECT DISTINCT value FROM dbo.fn_Split(@InspectionTypeList, ',')


DECLARE @InspectionVendor table
(
	Vendor int PRIMARY KEY
)
INSERT @InspectionVendor
SELECT DISTINCT value FROM dbo.fn_Split(@InspectionVendorList, ',')


DECLARE @InspectionCompany table
(
	Company int PRIMARY KEY
)
INSERT @InspectionCompany
SELECT DISTINCT value FROM dbo.fn_Split(@InspectionCompanyList, ',')

DECLARE @InspectionNotes table
(
	Note int PRIMARY KEY
)
INSERT @InspectionNotes
SELECT DISTINCT value FROM dbo.fn_Split(@InspectionNoteList, ',')



--clear electical problem if not true
if (@ElecticalProblem = 0)
	SET @ElecticalProblem = null


DECLARE @XmissionInspectionVisitIdList varchar(MAX)
SET @XmissionInspectionVisitIdList = ''

IF (NOT @XmissionInspectionProjectList IS NULL)
BEGIN
	SELECT
		@XmissionInspectionVisitIdList = @XmissionInspectionVisitIdList + CONVERT(varchar, [Vendor_InspVisit_XMissionStructure_PoleId]) + ','
	FROM
	(
		SELECT DISTINCT
			--Temporary change that we pull pole ids rather than structure ids
			Pole.Vendor_InspVisit_XMissionStructure_PoleId
		FROM tb_Vendor_InspVisit_XmissionStructures_Poles Pole
		INNER JOIN dbo.fn_Split(@XmissionInspectionProjectList, ',') Project
			ON Project.Value = Pole.Vendor_ProjectFK
		INNER JOIN 
		(
			SELECT 
				 Vendor_InspVisit_XMissionStructureId
				,Vendor_Visit_TypeFK
			FROM tb_Vendor_InspVisit_XMissionStructures Structure
			UNION
			SELECT -1, -1
		) AS Structure
			ON Structure.Vendor_InspVisit_XMissionStructureId = IsNULL(Pole.Vendor_InspVisit_XMissionStructureFk, -1)
		INNER JOIN @XmissionKv kv 
			ON kv.kv = isNULL(Structure.Vendor_Visit_TypeFK, -1)
		INNER JOIN @Maps Maps
			ON Maps.Map = isNULL(Pole.MapFk, -1)
		INNER JOIN @Sections Sections
			ON Sections.Section = ISNULL(Pole.SectionFk, -1)
		INNER JOIN @XmissionTops TopType
			ON TopType.TopType = isNULL(Pole.TopTypeFk, -1)
		INNER JOIN @XmissionSpecies Species
			ON Species.Species = isNULL(Pole.SpecieFk, -1)
		INNER JOIN @XmissionTreatment Treatment
			ON Treatment.Treatment = isNULL(Pole.TreatmentFk, -1)
		INNER JOIN @XmissionClass Class
			ON Class.Class = isNULL(Pole.ClassFk, -1)
		INNER JOIN @XmissionAge Age
			ON Age.Age = isNULL(Pole.[Year], -1)
		INNER JOIN 
		(
			SELECT 
				 Pole.Vendor_InspVisit_XMissionStructure_PoleId AS Vendor_InspVisit_XMissionStructure_PoleFk
				,Result.Vendor_InspVisit_XmissionStructures_ResultId
				,Result.Vendor_InspVisit_XmissionStructures_ResultTypeFk
				,Result.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk
				,IsNULL(Result.bElectrical, 0) AS bElectrical
			FROM tb_Vendor_InspVisit_XmissionStructures_Results Result
			RIGHT JOIN tb_Vendor_InspVisit_XmissionStructures_Poles Pole
				ON Result.Vendor_InspVisit_XMissionStructure_PoleFk = Pole.Vendor_InspVisit_XMissionStructure_PoleId
		) AS Result
			ON Result.Vendor_InspVisit_XMissionStructure_PoleFk = Pole.Vendor_InspVisit_XMissionStructure_PoleId
			AND Result.bElectrical = IsNULL(@ElecticalProblem, Result.bElectrical)
		INNER JOIN @InspectionResult InspectionResult
			ON InspectionResult.Result = IsNull(Result.Vendor_InspVisit_XmissionStructures_ResultTypeFk, -1)
		INNER JOIN @InspectionType InspectionType
			ON InspectionType.Type = IsNull(Result.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk, -1)
		LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_ResultsXNotes Notes
			ON Notes.Vendor_InspVisit_XmissionStructures_ResultFk = Result.Vendor_InspVisit_XmissionStructures_ResultId
		INNER JOIN @InspectionNotes InspectionNotes
			ON InspectionNotes.Note = IsNull(Notes.Vendor_InspVisit_XmissionStructures_NoteFk, -1)
		LEFT JOIN tb_Vendor_Contacts Contact
			ON Contact.Vendor_ContactId = Pole.Vendor_ContactFK
		INNER JOIN @InspectionVendor InspectionVendor
		 	ON InspectionVendor.Vendor = IsNull(Contact.Vendor_ContactId, -1)
		INNER JOIN @InspectionCompany InspectionCompany
		 	ON InspectionCompany.Company = IsNull(Contact.VendorFK, -1)
	) AS Ids
END



DECLARE @XmissionGPSVisitIdList varchar(MAX)
SET @XmissionGPSVisitIdList = ''

IF (NOT @XmissionGpsProjectList IS NULL)
BEGIN

	SELECT
		@XmissionGPSVisitIdList = @XmissionGPSVisitIdList + CONVERT(varchar, [Vendor_Visit_XMissionStructures_PoleId]) + ','
	FROM
	(
		SELECT DISTINCT
			--Temporary change that we pull pole ids rather than structure ids
			Pole.Vendor_Visit_XMissionStructures_PoleId
		FROM tb_Vendor_Visit_XMissionStructures_Poles Pole
		INNER JOIN tb_Vendor_Visit_XMissionStructures Structure 
			ON Pole.Vendor_Visit_XMissionStructureFK = Structure.Vendor_Visit_XMissionStructureId
		INNER JOIN dbo.fn_Split(@XmissionGpsProjectList, ',') Project
			ON Project.Value = Structure.Vendor_ProjectFK
		-- INNER JOIN @Maps Maps
		-- 	ON Maps.Map = IsNull(Pole.MapFk, -1)
		INNER JOIN @XmissionKv kv 
			ON kv.kv = IsNull(Structure.Vendor_Visit_TypeFK, -1)
		INNER JOIN @XmissionTops TopType
			ON TopType.TopType = IsNull(Pole.XMissionStructures_PoleTopTypeFk, -1)
		INNER JOIN @XmissionSpecies Species
			ON Species.Species = IsNull(Pole.XMissionStructures_PoleSpeciesFk, -1)
		INNER JOIN @XmissionTreatment Treatment
			ON Treatment.Treatment = IsNull(Pole.XMissionStructures_PoleTreatmentFk, -1)
		INNER JOIN @XmissionClass Class
			ON Class.Class = IsNull(Pole.XMissionStructures_PoleClassFk, -1)
		INNER JOIN @XmissionAge Age
			ON Age.Age = IsNull(Pole.[Year], -1)
		INNER JOIN tb_Vendor_Contacts Contact
			ON Contact.Vendor_ContactId = Structure.Vendor_ContactFK
		INNER JOIN @InspectionVendor InspectionVendor
		 	ON InspectionVendor.Vendor = IsNull(Contact.Vendor_ContactId, -1)
		INNER JOIN @InspectionCompany InspectionCompany
		 	ON InspectionCompany.Company = IsNull(Contact.VendorFK, -1)
	) AS Ids

END

DECLARE @DistibutionInspectionVisitIdList varchar(MAX)
SET @DistibutionInspectionVisitIdList = ''

IF (NOT @DistributionInspectionProjectList IS NULL)
BEGIN
	SELECT
		@DistibutionInspectionVisitIdList = @DistibutionInspectionVisitIdList + CONVERT(varchar, [Vendor_Distribution_Poles_PoleId]) + ','
	FROM
	(
		SELECT DISTINCT 
			 Vendor_Distribution_Poles_PoleId
		FROM tb_Vendor_Distribution_Poles Pole
		INNER JOIN dbo.fn_Split(@DistributionInspectionProjectList, ',') Project
			ON Project.Value = Pole.[ProjectFk] 
		INNER JOIN @Maps Maps
			ON Maps.Map = IsNull(Pole.MapFk, -1)
		INNER JOIN @Sections Sections
			ON Sections.Section = ISNULL(Pole.SectionFk, -1)
		INNER JOIN @DistributionSpecies Species
			ON Species.Species = IsNull(Pole.SpecieFk, -1)
		INNER JOIN @DistributionTreatment Treatment
			ON Treatment.Treatment = IsNull(Pole.TreatmentFk, -1)
		INNER JOIN @DistributionClass Class
			ON Class.Class = IsNull(Pole.ClassFk, -1)
		INNER JOIN @DistributionAge Age
			ON Age.Age = IsNull(Pole.[Year], -1)
--		INNER JOIN @InspectionVendor InspectionVendor
--			ON InspectionVendor.Vendor =  IsNull(Pole.InspectorFk, -1)
		INNER JOIN @InspectionCompany InspectionCompany
		 	ON InspectionCompany.Company = IsNull(Pole.VendorFk, -1)
		LEFT JOIN tb_Vendor_Distribution_PhasesXPole PolePhase
			ON PolePhase.Vendor_Distribution_PoleFk = Pole.Vendor_Distribution_Poles_PoleId
		INNER JOIN @DistributionPhase Phase
			ON Phase.Phase = IsNull(PolePhase.Vendor_Distribution_PhaseFk, -1)
		LEFT JOIN tb_Vendor_Distribution_TopsXPole PoleTop
			ON PoleTop.Vendor_Distribution_PoleFk = Pole.Vendor_Distribution_Poles_PoleId
		INNER JOIN @DistributionTops Tops
			ON Tops.TopType = IsNull(PoleTop.Vendor_Distribution_TopFk, -1)
		LEFT JOIN tb_Vendor_Distribution_InspectionResults Result
			ON Result.Vendor_Distribution_PoleFk = Pole.Vendor_Distribution_Poles_PoleId
			AND IsNull(Result.bElectrical, 0) = IsNULL(@ElecticalProblem, IsNull(Result.bElectrical, 0))
		INNER JOIN @InspectionResult InspectionResult
			ON InspectionResult.Result = IsNull(Result.Vendor_Distribution_ResultTypeFk, -1)
		INNER JOIN @InspectionType InspectionType
			ON InspectionType.Type = IsNull(Result.Vendor_Distribution_TypeFk, -1)
		LEFT JOIN tb_Vendor_Distribution_Results Notes
			ON Notes.Vendor_Distribution_PoleFk = Pole.Vendor_Distribution_Poles_PoleId
		INNER JOIN @InspectionNotes InspectionNotes
			ON InspectionNotes.Note = IsNull(Notes.Vendor_Distribution_ResultTypeFk, -1)
	) AS Ids

END

DECLARE @XmissionAssetIdList varchar(MAX)
SET @XmissionAssetIdList = ''


SELECT 
	 @XmissionAssetIdList = @XmissionAssetIdList + CONVERT(varchar, [Utility_Asset_XMissionStructureId]) + ',' --Utility_Asset_XMissionStructures_PoleId
FROM tb_Utility_Asset_XMissionStructures_Poles Pole
INNER JOIN tb_Utility_Asset_XMissionStructures Structure
	ON Structure.Utility_Asset_XMissionStructureId = Pole.Utility_Asset_XMissionStructureFK
INNER JOIN @Maps Maps
	ON Maps.Map = Pole.MapFk
INNER JOIN @Sections Sections
	ON Sections.Section = ISNULL(Pole.SectionFk, -1)
-- -- INNER JOIN @XmissionKv kv 
-- -- 	ON kv.kv = Structure.Vendor_Visit_TypeFK
INNER JOIN @XmissionTops TopType
	ON TopType.TopType = IsNull(Pole.XMissionStructures_PoleTopTypeFk, -1)
INNER JOIN @XmissionSpecies Species
	ON Species.Species = IsNull(Pole.XMissionStructures_PoleSpeciesFk, -1)
INNER JOIN @XmissionTreatment Treatment
	ON Treatment.Treatment = IsNull(Pole.XMissionStructures_PoleTreatmentFk, -1)
INNER JOIN @XmissionClass Class
	ON Class.Class = IsNull(Pole.XMissionStructures_PoleClassFk, -1)
INNER JOIN @XmissionAge Age
	ON Age.Age = IsNull(Pole.[Year], -1)
WHERE 
	@XmissionAssets = 1
	AND Structure.UtilityFK = @UtilityFk



DECLARE @DistributionAssetIdList varchar(MAX)
SET @DistributionAssetIdList = ''

SELECT
	 @DistributionAssetIdList = @DistributionAssetIdList + CONVERT(varchar, [Utility_Asset_DistributionId]) + ','
FROM
(
	SELECT DISTINCT
		 Utility_Asset_DistributionId
	FROM tb_Utility_Asset_Distributions Pole
	INNER JOIN 
		(
			SELECT Utility_Asset_DistributionFk
			FROM tb_Utility_Asset_Distribution_TopsXPole
			WHERE TopTypeFk IN (SELECT ISNULL(TopType, 1) FROM @DistributionTops)
		) PoleTop
		ON PoleTop.Utility_Asset_DistributionFk = Pole.Utility_Asset_DistributionId
	/*LEFT JOIN tb_Utility_Asset_Distribution_TopsXPole PoleTop
		ON PoleTop.Utility_Asset_DistributionFk = Pole.Utility_Asset_DistributionId*/
	LEFT JOIN tb_Utility_Asset_Distribution_PhasesXPole PolePhase
		ON PolePhase.Utility_Asset_DistributionFk = Pole.Utility_Asset_DistributionId
	INNER JOIN @Maps Maps
		ON Maps.Map = IsNull(Pole.MapFk, 1)
	INNER JOIN @Sections Sections
		ON Sections.Section = ISNULL(Pole.SectionFk, -1)
	INNER JOIN @DistributionPhase Phase
		ON Phase.Phase = IsNull(PolePhase.PhaseFk, 1)
	/*INNER JOIN @DistributionTops Tops
		ON Tops.TopType = IsNull(PoleTop.TopTypeFk, 1)*/
	INNER JOIN @DistributionSpecies Species
		ON Species.Species = IsNull(Pole.SpeciesFk, 1)
	INNER JOIN @DistributionTreatment Treatment
		ON Treatment.Treatment = IsNull(Pole.TreatmentFk, 1)
	INNER JOIN @DistributionClass Class
		ON Class.Class = IsNull(Pole.ClassFk, 1)
	INNER JOIN @DistributionAge Age
		ON Age.Age = IsNull(Pole.[Year], 1)
	WHERE
		@DistributionAssets = 1
		AND Pole.UtilityFK = @UtilityFk
) AS Ids



DECLARE @MeterAssetIdList varchar(MAX)
SET @MeterAssetIdList = ''

SELECT 
	 @MeterAssetIdList = @MeterAssetIdList + CONVERT(varchar, [Utility_Asset_MeterId]) + ','
FROM tb_Utility_Asset_Meters Meter
INNER JOIN @Maps Maps
	ON Maps.Map = IsNull(Meter.MapFk, -1)
INNER JOIN @Sections Sections
	ON Sections.Section = ISNULL(Meter.SectionFk, -1)
WHERE
	@MeterAssets = 1
	AND UtilityFK = @UtilityFk


DECLARE @TransformerAssetIdList varchar(MAX)
SET @TransformerAssetIdList = ''

SELECT 
	 @TransformerAssetIdList = @TransformerAssetIdList + CONVERT(varchar, [Utility_Asset_TransformerId]) + ','
FROM 
	tb_Utility_Asset_Transformers Transformer
WHERE
	@TransformerAssets = 1
	AND UtilityFK = @UtilityFk

SELECT 
	 @XmissionInspectionVisitIdList AS XmissionInspectionVisitIdList
	,@XmissionGPSVisitIdList AS XmissionGPSVisitIdList
	,@DistibutionInspectionVisitIdList AS DistibutionInspectionVisitIdList
	,@XmissionAssetIdList AS XmissionAssetIdList
	,@DistributionAssetIdList AS DistributionAssetIdList
	,@MeterAssetIdList AS MeterAssetIdList
	,@TransformerAssetIdList AS TransformerAssetIdList



GO